﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using DocumentFormat.OpenXml.Spreadsheet;
using MAPDavy.Entities;

namespace MAPDavy.Repository
{
    public class Library
    {
        static readonly CultureInfo Culture = new CultureInfo("en-US");

        public static List<MAPTemplate> RetrieveMAPS(Worksheet worksheet, SharedStringTable sharedStringTable)
        {
            var result = new List<MAPTemplate>();

            var dataRows = from row in worksheet.Descendants<Row>()
                           where row.RowIndex > 1 //The table begins on line 5
                           select row;

            foreach (var row in dataRows)
            {
                var textValues = new List<string>();

                //where cell.CellValue != null
                foreach (var cell in from cell in row.Descendants<Cell>() select cell)
                {
                    var refe = cell.CellReference.Value;
                    if (refe != "O" + row.RowIndex)
                    {
                        try
                        {
                            if (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString)
                            {
                                textValues.Add(sharedStringTable.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText);
                            }
                            else
                            {
                                var val = cell.CellValue != null ? cell.CellValue.InnerText : string.Empty;

                                textValues.Add(val);
                            }
                        }
                        catch (Exception)
                        {
                            throw;
                        }
                    }
                }

                var textArray = textValues.ToArray();

                if (textArray.Count() <= 0) continue;

                var map = new MAPTemplate()
                {
                    TeacherLastName = textArray[2],
                    TeacherFirtsName = textArray[3],
                    StudentGrade = textArray[4],
                    ClassName = textArray[6],
                    StudentLastName = textArray[8],
                    StudentFirtsName = textArray[9]
                };

                if (textArray[1].Length == 5) map.TeacherID = textArray[1];
                if (textArray[0] != string.Empty) map.Year = Int32.Parse(textArray[0]);
                if (textArray[5] != string.Empty) map.ClassID = Int32.Parse(textArray[5]);
                if (textArray[7] != string.Empty) map.StudentID = Int32.Parse(textArray[7]);
                if (textArray[10] != string.Empty) map.StudentDateBirth = DateTime.FromOADate(double.Parse(textArray[10]));
                if (textArray[10] != string.Empty) map.DateBirth = DateTime.FromOADate(double.Parse(textArray[10])).ToShortDateString();
                if (textArray[11] != string.Empty)
                {
                    if (textArray[11].ToUpper() == "M" || textArray[11].ToUpper() == "F")
                    {
                        map.StudentGender = char.Parse(textArray[11].ToUpper());
                    }
                }
                if (textArray[12] != string.Empty) map.FechaIngreso = DateTime.FromOADate(double.Parse(textArray[12].Replace("-","/")));
                if (textArray[12] != string.Empty) map.FechaIngresoLabel = DateTime.FromOADate(double.Parse(textArray[12].Replace("-", "/"))).ToShortDateString();
                if (textArray.Count() == 14)
                {
                    if (textArray[13] != string.Empty) map.DavyID = Int32.Parse(textArray[13].ToString());
                }                

                result.Add(map);
            }

            return result;
        }

        public static List<MAPRit> RetrieveTemplateRit(Worksheet worksheet, SharedStringTable sharedStringTable)
        {
            var result = new List<MAPRit>();

            var dataRows = from row in worksheet.Descendants<Row>()
                           where row.RowIndex > 1 //The table begins on line 5
                           select row;

            foreach (var row in dataRows)
            {
                var textValues = new List<string>();

                //where cell.CellValue != null
                foreach (var cell in from cell in row.Descendants<Cell>() select cell)
                {
                    var refe = cell.CellReference.Value;
                    if (refe != "E" + row.RowIndex)
                    {
                        try
                        {
                            if (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString)
                            {
                                textValues.Add(sharedStringTable.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText);
                            }
                            else
                            {
                                var val = cell.CellValue != null ? cell.CellValue.InnerText : string.Empty;

                                textValues.Add(val);
                            }
                        }
                        catch (Exception)
                        {
                            throw;
                        }
                    }
                }

                var textArray = textValues.ToArray();

                if (textArray.Count() <= 0) continue;

                var map = new MAPRit()
                {
                    Student = textArray[1],
                    Grade = textArray[3]
                };

                if (textArray[0] != string.Empty)
                {
                    if (textArray[0].Length == 6)
                    {
                        map.StudentID = Int32.Parse(textArray[0]);
                    }
                }                
                
                if (textArray[2] != string.Empty) map.RIT = Int32.Parse(textArray[2]);

                result.Add(map);
            }

            var list = result.GroupBy(r => r.StudentID).Select(s => s.FirstOrDefault()).ToList();

            return list;
        }
    }
}
